[DAY3]SQL的新手懶人筆記 (大寫為內建語法)

21.ORDER BY可用來排序資料，如果是字串變數會照A~Z去排序，若開頭有數字，則排在A之前。若是數字變數，則由小排到大。

SELECT title
FROM films
ORDER BY release_year DESC;

22.在ORDER BY的變數之後加上DESC，則資料會反向排序。(由大到小)

SELECT name
FROM people
ORDER BY name DESC;

23.GROUP BY 是所有整理資料的語言中，最重要的語法之一，因為可以利用GROUP BY照自己希望的方式把資料做分組。以下則是把資料按照sex這個變數去做分組，再去output出自己想要的運算與結果。

SELECT sex, count(*)
FROM employees
GROUP BY sex;

24.HAVING的語法與WHERE相似，但是我們會在有GROUP BY的時候使用HAVING，使用WHERE的話，程式碼會無法運行。

SELECT release_year
FROM films
GROUP BY release_year
HAVING COUNT(title) > 10;

25.LIMIT 後面接上數字，表示寫列出多少個資料。

SELECT country, avg(budget) AS avg_budget, avg(gross) AS avg_gross
FROM films  
GROUP BY country
HAVING COUNT(title) > 10
ORDER BY country
LIMIT 5;

26.FROM ... INNER JOIN可以把兩個不同的table合併在一起，合併的方式取決於ON後面，來自不同table的同樣意涵的variable。(在叫某table的variable時，需用"."座連接，ex:table1.variable = table2.variable)

SELECT * 
FROM cities
  INNER JOIN countries
    ON cities.country_code = countries.code;

27.在INNER JOIN之後可以用AS設定簡寫，如此一來在SELECT之後就可以使用較為簡短的語法去描述。

SELECT c.code AS country_code, name, year, inflation_rate
FROM countries AS c
  INNER JOIN economies AS e
    ON c.code = e.code;

28.當兩個table有相同名稱的變數可以做合併時，我們可以用USING去省略語法。

SELECT c.name AS country, continent, l.name AS language, official
  From countries AS c
  INNER JOIN languages AS l
    USING(code)

29.以下INNER JOIN的語法可稱為SELF JOIN，意思是把自己與自己做合併，也就是把想要變數的所有組合的可能性都給列出來。

SELECT p1.country_code, p1.size AS size2010, p2.size AS size2015
From populations AS p1
  INNER JOIN populations AS p2
    USING(country_code);

30.INNER JOIN ... ON 之後也可加入一些想要的條件(布林運算)，去得到你想要的資料。

SELECT p1.country_code,
       p1.size AS size2010, 
       p2.size AS size2015,
       ((p2.size - p1.size)/p1.size * 100.0) AS growth_perc
FROM populations AS p1
  INNER JOIN populations AS p2
    ON p1.country_code = p2.country_code
        AND p1.year = p2.year - 5;